import pymysql
import json

filename="items.json"
with open(filename) as f_obj:
    items=json.load(f_obj)
    # print(item)
    #列表去重
    item=[]
    [item.append(i) for i in items if i not in item]
    print(len(item))
    
    # 华为云  '139.9.162.53','root','yxonline@2019','crawler_db'
    db=pymysql.connect('localhost','root','root','scrapy_items')
    print("数据库已连接~")
    cursor=db.cursor() 
    sql="""INSERT INTO items(app_name,author_name,category_name,pkg_name,apk_url,icon_url,average_rating,app_downcount,images,apk_publish_time,version_name,app_detail,permission_info)
    VALUES %s"""
    # 格式化item数据
    sf = ''
    # 数据量较大时，建议使用切片分段插入数据库
    for dic in item[1200:1400]:
        tup=''
        for key,value in dic.items():
            # detail中有转义字符，需要多包两层转义 否则插入sql报错
            if key=='appDetail':
                tup=tup+'"'+"'"+'"'+(str(dic[key]))+'"'+"'"+'"'+',' 
            else:
            # 插入的sql  如果是字符串必须带有引号
                tup=tup+'"'+(str(dic[key]))+'"'+',' 
        tup=tup.rstrip(',')
        sf=sf+'('+tup+')'+','
    sf = sf.rstrip(',')
    # print(sf)
    sql2=sql % sf
    # print(sql2)
    try:
        print('进入try！')
        cursor.execute(sql2)
        db.commit()
        print('数据已插入数据库！')
    except:
        print('数据插入失败！')
        db.rollback()
    db.close()
    print('数据库已关闭！')

    

